<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH W_USER AS
  (SELECT t.*,
  row_number() over(order by T_ORG.code, T.code) rn ,
  t_org.code AS orgCode,
  t_org.name AS orgName
FROM c1_user t
LEFT JOIN c1_org t_org ON (t_org.id = t.org_id)
WHERE 1 = 1
<#if StringUtils.isNotBlank(code)>
AND (instr(t.code, :code) > 0 or instr(t.name, :code) > 0)
</#if>
<#if StringUtils.isNotBlank(stateIds)>
AND instr(:stateIds, NVL(t.state_id,(SELECT w.id from c1_dictionary w where w.code = '1' and exists(select 1 from c1_dictionary_type e where e.code = 'user_state' and e.id = w.type_id )))) > 0
</#if>
<#if StringUtils.isNotBlank(orgId)>
AND t.org_id = :orgId
</#if>
<#if StringUtils.isNotBlank(orgCode)>
AND (instr(T_ORG.code, :orgCode) > 0 OR instr(T_ORG.name, :orgCode)   > 0)
</#if>
  )
SELECT T.*,
	TO_CHAR(NVL(T.CREATED_DATE, T.BEGIN_DATE), 'yyyy-mm-dd') as createdDateStr,
	TO_CHAR(T.END_DATE, 'yyyy-mm-dd') as endDateStr,
	Trunc(MONTHS_BETWEEN(SYSDATE, T.BIRTHDAY)/12) as now_age,
  	(select s.name from c1_dictionary s where s.id = NVL(t.state_id,(SELECT s_dict.id from c1_dictionary s_dict where s_dict.code = '1' and exists(select 1 from c1_dictionary_type e where e.code = 'user_state' and e.id = s_dict.type_id )))) as state_name,
    T_SEX.code         AS SEX_CODE,
    T_SEX.NAME         AS SEX_NAME
    FROM W_USER T
  LEFT JOIN c1_dictionary T_SEX
  ON ((sysdate BETWEEN T_SEX.BEGIN_DATE AND T_SEX.END_DATE)
  AND T_SEX.id = t.SEX_ID)
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>